package com.action.proceso6;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.bean.proceso6.OrdenDeServicio;
import com.conexion.DbConnection;
import com.mysql.jdbc.Connection;

public class OrdenDeServicioAction {

	private final String TABLA = "ordendeservicio";

	// metodo para insertar orden de servicio

	public int insertar(OrdenDeServicio obj) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = DbConnection.getConnection();

			String sql = "INSERT INTO " + TABLA + " VALUES(?, ?, ?, ?, ?, ?)";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, obj.getCodigo());
			pstm.setString(2, obj.getFecha());
			pstm.setString(3, obj.getIdproveedor());
			pstm.setString(4, obj.getObservaciones());
			pstm.setDouble(5, obj.getTotal());
			pstm.setString(6, obj.getIdtrabajador());

			return pstm.executeUpdate();

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeServicioAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	// metodo para listar orden de servicio

	public ArrayList<OrdenDeServicio> listar() {

		Connection conn = null;
		PreparedStatement pstm = null;

		ArrayList<OrdenDeServicio> lista = new ArrayList<OrdenDeServicio>();
		try {
			conn = DbConnection.getConnection();

			String sql = "Select * FROM " + TABLA;
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				lista.add(new OrdenDeServicio(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getDouble(5), rs
								.getString(6)));
			}

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeServicioAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lista;
	}

	// metodo para buscar orden servicio

	public OrdenDeServicio buscar(String codigo) {

		Connection conn = null;
		PreparedStatement pstm = null;
		OrdenDeServicio os = null;

		try {

			conn = DbConnection.getConnection();

			String sql = "Select * FROM " + TABLA + " where codigo=?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, codigo);

			ResultSet rs = pstm.executeQuery();

			if (rs.next())
				os = new OrdenDeServicio(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getDouble(5),
						rs.getString(6));

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeServicioAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return os;
	}
	// metodo para actualizar la orden servicio

	public int actualizar(OrdenDeServicio obj) {
		Connection conn = null;
		PreparedStatement pstm = null;

		try {

			conn = DbConnection.getConnection();

			String sql = "Update on "
					+ TABLA
					+ " set codigo=?, fecha=?, concepto=?, proveedor=?, referencia=?,  total=?, trabajador=? ";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, obj.getCodigo());
			pstm.setString(2, obj.getFecha());
			pstm.setString(3, obj.getIdproveedor());
			pstm.setString(4, obj.getObservaciones());
			pstm.setDouble(5, obj.getTotal());
			pstm.setString(6, obj.getIdtrabajador());

			return pstm.executeUpdate();

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeServicioAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	// metodo para eliminar orden servicio

	public int eliminar(String codigo) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = DbConnection.getConnection();

			String sql = "delete from " + TABLA + " where codigo=?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, codigo);

			return pstm.executeUpdate();

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeServicioAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	public ArrayList<OrdenDeServicio> listar(String criterio) {
		Connection conn = null;
		PreparedStatement pstm = null;

		ArrayList<OrdenDeServicio> lista = new ArrayList<OrdenDeServicio>();
		try {
			conn = DbConnection.getConnection();

			String sql = "Select * FROM " + TABLA + "where codigo like ?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, "%" + criterio + "%");

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				lista.add(new OrdenDeServicio(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getDouble(5), rs
								.getString(6)));
			}

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeServicioAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lista;
	}

}
